<?php
class pro_kontrol_air_model extends MY_Model {
	var $nama_tabel = 'ADMIN_PRIMKOKAS.PRO_PEMAKAIAN_AIR';
	function getApp()
	{
		$ls_sql	= "	SELECT	APP_ID value,
							APP_NAME display
					FROM	ADMIN_PRIMKOKAS.IT_APPLICATION
					ORDER BY APP_NAME";
					
		$result	= $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			return $result->result_array();
		}else{
			return array();
		}
	}

	function getLokasi()
	{
		$ls_sql	= "	SELECT 	distinct a.id_lokasi,
							a.lokasi_tempat value,
							b.lokasi_tempat display
					FROM	pro_sewa_gedung a
					JOIN pro_lokasi_tempat b ON a.id_lokasi = b.id_lokasi
					WHERE a.status_sewa = 'Sudah Di Sewa'
					ORDER BY a.lokasi_tempat ASC";
					
		$result	= $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			return $result->result_array();
		}else{
			return array();
		}
	}

	function getNotoko($id)
	{
		$ls_sql	= "	SELECT 	a.id_lokasi as key_lokasi,
							b.lokasi_tempat as id_lokasi,
							a.no_toko as no_toko
					FROM pro_tenant a 
					JOIN pro_lokasi_tempat b ON a.id_lokasi = b.id_lokasi
					WHERE a.status_tenant = 'Tenant' AND a.id_lokasi = '".$id."'
					ORDER BY a.no_toko ASC ";
					
		$result	= $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			return json_encode($result->result_array());
		}else{
			return json_encode(array());
		}
	}

	function getNotokoEdit($id)
	{
		$ls_sql	= "	SELECT 	a.id_lokasi as key_lokasi,
							b.lokasi_tempat as id_lokasi,
							a.no_toko as no_toko
					FROM pro_tenant a 
					JOIN pro_lokasi_tempat b ON a.id_lokasi = b.id_lokasi
					WHERE a.status_tenant = 'Tenant' AND a.lokasi_tempat = '".$id."'
					ORDER BY a.no_toko ASC ";
					
		$result	= $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			return json_encode($result->result_array());
		}else{
			return json_encode(array());
		}
	}

	function dataTenant($id,$lokasi_tempat)
	{
		$ls_sql	= " SELECT 	a.nama_tenant as nama_tenant,
							a.jenis_usaha as jenis_usaha
					FROM pro_tenant a 
					JOIN pro_lokasi_tempat b ON a.lokasi_tempat = b.id_lokasi
					WHERE a.status_tenant = 'Tenant' AND a.lokasi_tempat = '".$lokasi_tempat."' AND a.no_toko = '".$id."'";

		$result	= $this->db->query($ls_sql);
		$data_tenant = $result->row_array(); 

	}

	function GetJSON1()
	{
		$ls_sql	= "	SELECT	a.id_pemakaian_air,a.no_toko,a.nama_tenant,a.meter_awal_pemakaian,a.meter_akhir_pemakaian,a.no_air,
							a.total_meter_pemakaian,b.lokasi_tempat,a.tanggal,a.jenis_usaha,a.status,a.no_tenant,a.id_lokasi
					FROM	pro_pemakaian_air a
					JOIN 	pro_lokasi_tempat b ON a.id_lokasi = b.id_lokasi
					WHERE	UPPER(a.status) = '0' AND
							(UPPER(a.no_toko) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(a.id_pemakaian_air) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(a.nama_tenant) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(a.no_air) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(a.meter_awal_pemakaian) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(a.meter_akhir_pemakaian) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(a.total_meter_pemakaian) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(a.lokasi_tempat) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(a.tanggal) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(a.jenis_usaha) LIKE '%".strtoupper(post('keyword'))."%')
					ORDER BY ".(post('sort')=='' ? 'a.id_pemakaian_air' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));	
		return json_encode( $this->paging($ls_sql) );
	}

	function GetJSON()
	{
		$ls_sql	= "	SELECT	a.id_pemakaian_air,a.no_air,a.no_toko,a.nama_tenant,a.meter_awal_pemakaian,a.meter_akhir_pemakaian,
							a.total_meter_pemakaian,b.lokasi_tempat,a.tanggal,a.jenis_usaha,a.status,a.no_tenant,a.id_lokasi
					FROM	pro_pemakaian_air a
					JOIN 	pro_lokasi_tempat b ON a.id_lokasi = b.id_lokasi
					WHERE	UPPER(a.status) = '0' AND
							(UPPER(a.no_toko) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(a.id_pemakaian_air) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(a.nama_tenant) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(a.no_air) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(a.meter_awal_pemakaian) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(a.meter_akhir_pemakaian) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(a.total_meter_pemakaian) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(a.lokasi_tempat) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(a.tanggal) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(a.jenis_usaha) LIKE '%".strtoupper(post('keyword'))."%')
					ORDER BY ".(post('sort')=='' ? 'a.id_pemakaian_air' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));	
		return json_encode( $this->paging($ls_sql) );
	}

	function GetJSON2()
	{
		$ls_sql	= "	SELECT	*
					FROM	pro_tenant  
					WHERE	UPPER(status_tenant) = 'TENANT' AND
							(UPPER(no_toko) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(lokasi_tempat) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(nama_tenant) LIKE '%".strtoupper(post('keyword'))."%')
					ORDER BY ".(post('sort')=='' ? 'no_toko' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));	
		return json_encode( $this->paging($ls_sql) );
	}

	function Add($arr_data){
		$this->db->insert($this->nama_tabel, $arr_data);
	}

	function Update($arr_data, $arr_rule){
		$a = $this->db->update($this->nama_tabel, $arr_data, $arr_rule);
	}

	function DeleteSingle($arr_rule) {
		try {
			if(	intval($this->get_data("SELECT COUNT(1) FROM ADMIN_PRIMKOKAS.PRO_PEMAKAIAN_LISTRIK WHERE id_pemakaian_air = '".$arr_rule['id_pemakaian_air']."'"))<1)
			{
				return $this->error_delete.' [id: '.$arr_rule['id_pemakaian_air']."]<br /> SELECT COUNT(1) FROM ADMIN_PRIMKOKAS.PRO_PEMAKAIAN_LISTRIK WHERE id_pemakaian_air = '".$arr_rule['id_pemakaian_air']."'";
			}else{
				$this->db->delete('ADMIN_PRIMKOKAS.PRO_PEMAKAIAN_LISTRIK', array('id_pemakaian_air' => $arr_rule['id_pemakaian_air']));
				return '';
			}
		} catch(Exception $e) {
			return $e->getMessage();
		}
	}

	function check_no_toko($lokasi_tempat){
		$check_last_noToko = "	SELECT a.no_toko as limit_no_toko FROM(
								SELECT no_toko FROM PRO_TENANT 
								WHERE STATUS_TENANT = 'Tenant' AND LOKASI_TEMPAT = '".$lokasi_tempat."'
								ORDER BY case when regexp_instr(NO_TOKO,'[0-9]+') = 1 then 
									 to_number(regexp_substr(NO_TOKO,'[0-9]+')) 
								else 
									 null 
								end DESC, NO_TOKO DESC) a 
								WHERE ROWNUM <=1 ";

		$check_result = $this->db->query($check_last_noToko);
		return $check_result->row_array();

	}

	function GetCetakList(){
		
		$result = $this->db->query("  	SELECT *
										FROM pro_pemakaian_air
										ORDER BY id_lokasi DESC");

		return $result->result_array($result);
		
	}

}
?>